--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

-- CALL uvse_ExistsOrCreate('ART_GENE');

 DELETE FROM se_ART_GENE;


 -- select &erp.isnumeric(conv),conv,p.* from uv_pproduits p WHERE codgestion='2'  and &erp.isnumeric(conv)!=1
 -- select * from se_ART_GENE where gene_cod like 'P11094101279'
 -- select * from &erp.ART_GENE where gene_cod like 'CAT%LOGO%'
 -- select * from se_ART_GENE


 INSERT INTO se_ART_GENE
 (SELECT distinct  SubStr(p.prodbase,1,Length(p.prodbase)-1) gene_cod,

         p.indice gene_ind,
         null gene_pere_cod,
         NULL gene_pere_ind,
         p.unl unl_cod,
         Decode(p.codgestion,1,'SANSVRT_','VRT_')
         || (SELECT Decode(Count(1),0,'NC','C') FROM uv_pcomm_prod c WHERE c.REFSTOCK=p.id) prfgene_cod,


         0 comp_num,
         Nvl(p.uns,p.unl) unr_cod,
         0 enreg_id,
         p.unc una_cod,
         1 numerunruna_conv,
         SubStr(p.conv,InStr(p.conv,'/',1)+1,99)+0 denomunruna_conv,
         p.unn unn_cod,
         'N' enreg_s,
         To_Date('31/12/1967','dd/mm/yyyy')+p.datap debind_dat,
         /*Decode(m.datfin,NULL,NULL,To_Date('31/12/1967','dd/mm/yyyy')+m.datfin )*/null finind_dat,
         SubStr(p.conv3,InStr(p.conv3,'/',1)+1,99)+0 numerunrunn_conv,
         1 denomunrunn_conv,
         SubStr(p.conv2,InStr(p.conv2,'/',1)+1,99)+0 numerunrunl_conv,
         1 denomunrunl_conv,
         'N' editchapitrevrt_ok,
         'N' horsse_ok
         /*,conv.**/
    FROM uv_pproduits_statut_actuel s,uv_pproduits p,uv_pref_sto_modelcom conv,UV_CMODELES m
    WHERE p.codgestion in ('2','1') AND p.prodbase=p.ID
      AND s.id=p.id and (s.statut<900 OR statut IS NULL)
      AND conv.ID(+) =p.id AND m.id(+)=conv.MODELCOM
      /*AND p.prodbase='SACHET0'*/
     );


 UPDATE se_ART_GENE tf SET enreg_id=(select enreg_id FROM &erp.ART_GENE f WHERE f.gene_cod=tf.gene_cod AND f.gene_ind=tf.gene_ind) ;
 UPDATE se_ART_GENE tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL;

 UPDATE se_ART_GENE tf SET comp_num=(select comp_num FROM &erp.ART_GENE f WHERE f.gene_cod=tf.gene_cod AND f.gene_ind=tf.gene_ind) ;
 UPDATE se_ART_GENE tf SET comp_num=&outils.art_comp.NEXTVAL WHERE comp_num IS NULL;




 commit;
 
 

